package org.javaforever.poitranslator.core;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.light.core.ApplicationContextXml;
import org.light.core.SpringMVCController;
import org.light.domain.Domain;
import org.light.domain.Dropdown;
import org.light.domain.Field;
import org.light.domain.ManyToMany;
import org.light.domain.Pair;
import org.light.domain.Prism;
import org.light.domain.Project;
import org.light.domain.ValidateInfo;
import org.light.easyui.EasyUIHomePageTemplate;
import org.light.easyui.EasyUIPageTemplate;
import org.light.exception.ValidateException;
import org.light.generator.DBDefinitionGenerator;
import org.light.generator.JsonPagingGridJspTemplate;
import org.light.generator.MysqlDBDefinitionGenerator;
import org.light.oracle.core.OracleDomainDecorator;
import org.light.oracle.core.OraclePrism;
import org.light.oracle.generator.Oracle11gDBDefinitionGenerator;
import org.light.utils.BooleanUtil;
import org.light.utils.DomainUtil;
import org.light.utils.StringUtil;

public class ProjectExcelWorkBook {
	protected Project project;
	protected String username;
	protected String password;
	protected String filename;
	
	protected final static String[] forbiddenwords = { "abstract", "assert", "boolean", "break", "byte", "case",
			"catch", "char", "class", "const", "continue", "default", "do", "double", "else", "enum", "extends",
			"final", "finally", "float", "for", "if",

			"implements", "import", "instanceof", "int", "interface", "long", "native", "new", "package", "private",
			"protected", "public", "return", "short", "static", "strictfp", "super", "switch",

			"synchronized", "this", "throw", "throws", "transient", "try", "void", "volatile", "while", "byValue",
			"cast", "false", "future", "generic", "inner", "operator", "outer", "rest", "true", "var", "goto", "const",
			"null" };

	protected final static String[] sqlKeyWords = { "alter", "and", "as", "asc", "between", "by", "count", "create",
			"delete", "desc", "distinct", "drop", "from", "group", "having", "in", "insert", "into", "is", "join",
			"like", "not", "on", "or", "order", "select", "set", "table", "union", "update", "values", "where", "limit",
			"bool", "boolean", "bit", "blob", "enum", "long", "longblob", "longtext", "medium", "mediumblob",
			"mediumint", "mediumtext", "time", "timestamp", "tinyblob", "tinyint", "tinytext", "text", "bigint", "int",
			"int1", "int2", "int3", "int4", "int8", "integer", "float", "float4", "float8", "double", "char",
			"varbinary", "varchar", "varcharacter", "precision", "real", "date", "datetime", "year", "unsigned",
			"signed", "decimal", "numeric", "false", "true", "null", "unknown", "date", "time", "timestamp" };
	
	public static boolean isSqlKeyword(String notion) {
		for (String word : sqlKeyWords) {
			if (word.equals(notion))
				return true;
		}
		return false;
	}

	public static boolean isForbidden(String notion) {
		for (String word : forbiddenwords) {
			if (word.equals(notion))
				return true;
		}
		return false;
	}
	
	public static void decorateDropdowns(List<Domain> domainList) throws ValidateException {
		for (Domain d : domainList) {
			for (Field f : d.getFieldsWithoutId()) {
				if (f instanceof Dropdown) {
					Dropdown dp = (Dropdown) f;
					System.out.println("JerryDebugger:dropdown:" + dp.getTargetName());
					Domain t = DomainUtil.findDomainInList(domainList, dp.getTargetName());
					dp.decorate(t);
				}
			}
		}
	}
	
	public static List<Prism> generatePrismsByDomains(List<Domain> domainList, DBDefinitionGenerator dbdg,
			Project project) throws Exception {
		if (project.getDbType() == null || "".equals(project.getDbType()) || "mysql".equalsIgnoreCase(project.getDbType())) {
			List<Prism> prisms = new ArrayList<Prism>();
			Set<Domain> projectDomainSet = new TreeSet<Domain>();
			projectDomainSet.addAll(domainList);
			for (Domain d : domainList) {
				d.decorateDomainWithLabels();
				Prism p = new Prism();
				p.setPackageToken(d.getPackageToken());
				p.setStandardName(d.getCapFirstDomainName() + "Prism");
				p.setDomain(d);
				p.setDbDefinitionGenerator(dbdg);
				p.setProjectDomains(projectDomainSet);
				p.setResolution(project.getResolution());
				p.setTitle(project.getTitle());
				p.setSubTitle(project.getSubTitle());
				p.setFooter(project.getFooter());
				p.setCrossOrigin(project.getCrossOrigin());
				p.generatePrismFromDomain();
				prisms.add(p);
			}
			return prisms;
		} else if ("oracle".equalsIgnoreCase(project.getDbType())) {
			List<Prism> prisms = new ArrayList<Prism>();
			Set<Domain> projectDomainSet = new TreeSet<Domain>();
			projectDomainSet.addAll(domainList);
			for (Domain d : domainList) {
				d.decorateDomainWithLabels();
				OraclePrism p = new OraclePrism();
				p.setPackageToken(d.getPackageToken());
				p.setStandardName(d.getCapFirstDomainName() + "Prism");
				p.setDomain(OracleDomainDecorator.decorateOracleDomain(d));
				p.setDbDefinitionGenerator(dbdg);
				p.setProjectDomains(OracleDomainDecorator.decorateOracleDomainSet(projectDomainSet));
				p.setResolution(project.getResolution());
				p.setTitle(project.getTitle());
				p.setSubTitle(project.getSubTitle());
				p.setFooter(project.getFooter());
				p.setCrossOrigin(project.getCrossOrigin());
				p.generatePrismFromDomain();
				prisms.add(p);
			}
			return prisms;
		} else {
			ValidateInfo info = new ValidateInfo();
			info.addCompileError("未支持的数据库类型。");
			throw new ValidateException(info);
		}
	}
	
	public static Domain findDomainFromListByStandardName(List<Domain> domainList, String standardName)
			throws ValidateException {
		for (Domain d : domainList) {
			if (d.getStandardName().equals(standardName))
				return d;
		}
		ValidateInfo info = new ValidateInfo();
		info.addCompileError("在域对象列表找不到域对象" + standardName + "。");
		throw new ValidateException(info);
	}

	public Project translate(HSSFWorkbook book) throws Exception {
		project = new Project();
		project = translateProjectMetaData(book.getSheet("project"), project);
		List<Domain> domainList = new ArrayList<Domain>();

		ValidateInfo info0 = new ValidateInfo();
		for (int i = 1; i < book.getNumberOfSheets(); i++) {
			try {
				HSSFSheet sheet = book.getSheetAt(i);
				if (sheet.getSheetName().toLowerCase().contains("domain")) {
					Domain domain = translateDomain(sheet, project.getDbType());
					domain.setDbPrefix(project.getDbPrefix());
					domain.setPackageToken(project.getPackageToken());
					// output data
					ArrayList<Domain> dataList = new ArrayList<Domain>();
					dataList.addAll(readDomainListWithData(sheet, domain, project.getDbType()));
					System.out.println("JerryDebug:datalistsize:" + dataList.size());
					for (Domain d : dataList) {
						System.out.println(d.getDomainName().getFieldValue());
					}
					project.addDataDomains(dataList);
					// end ouput
					domainList.add(domain);
				}
			} catch (ValidateException e) {
				info0.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info0.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}

		if (!info0.success())
			throw new ValidateException(info0);

		project.setDomains(domainList);

		Project project2 = new Project(project.getStandardName(), project.getPackageToken(),
				project.getTechnicalstack(), project.getDbUsername(), project.getDbPassword(),
				project.isEmptypassword(), project.getDbName(), project.getDbType());
		project2.setTitle(project.getTitle());
		project2.setSubTitle(project.getSubTitle());
		project2.setFooter(project.getFooter());
		project2.setCrossOrigin(project.getCrossOrigin());
		project2.setResolution(project.getResolution());
		DBDefinitionGenerator dbdg ;
		if (project.getDbType().equalsIgnoreCase("oracle")) {
			dbdg = new Oracle11gDBDefinitionGenerator();
		}else {
			dbdg = new MysqlDBDefinitionGenerator();
		}
		dbdg.setDbName(project.getDbName());
		ApplicationContextXml axml = new ApplicationContextXml();

		decorateDropdowns(domainList);
		if (project.getPackageToken() == null || "".equals(project.getPackageToken())) {
			ValidateInfo info = new ValidateInfo();
			info.addCompileError("没有设置PackageToken！");
			throw new ValidateException(info);
		} else {
			String dbPrefix = project.getDbPrefix();
			for (int m = 0; m < domainList.size(); m++) {
				domainList.get(m).setDbPrefix(dbPrefix);
				domainList.get(m).setPackageToken(project.getPackageToken());
			}
		}
		List<Prism> prismList = generatePrismsByDomains(domainList, dbdg, project2);
		project2.setPrisms(prismList);
		TreeSet<SpringMVCController> mycontrollers = new TreeSet<SpringMVCController>();
		EasyUIHomePageTemplate homepage = project2.getHomepage();
		homepage.setTitle(project.getTitle());
		homepage.setSubTitle(project.getSubTitle());
		homepage.setFooter(project.getFooter());
		homepage.setResolution(project.getResolution());
		project2.setHomepage(homepage);
		
		for (Prism p : prismList) {
			p.setTitle(project2.getTitle());
			p.setSubTitle(project2.getSubTitle());
			p.setFooter(project2.getFooter());
			p.setResolution(project2.getResolution());
			
			List<JsonPagingGridJspTemplate> jList = p.getJsonjsptemplates();
			for (JsonPagingGridJspTemplate jpt:jList) {
				EasyUIPageTemplate eupt = (EasyUIPageTemplate) jpt;
				eupt.setResolution(project2.getResolution());
			}
			
			SpringMVCController f = p.getController();
			if (!StringUtil.isBlank(project2.getCrossOrigin())){
				p.setCrossOrigin(project2.getCrossOrigin());
				f.decorateCrossOrigin(project2.getCrossOrigin());
			}
			p.setController(f);
			mycontrollers.add(f);
			
			//Mirgate from SGSCompiler
			Set<Pair> mtmSlaveNames = new TreeSet<Pair>();
			for (ManyToMany mtm:p.getDomain().getManyToManies()){
				mtm.setResolution(project2.getResolution());
				mtmSlaveNames.add(new Pair(p.getDomain().getStandardName(),mtm.getManyToManySalveName()));
			}
			p.setManyToManySlaveNames(mtmSlaveNames);
		}

		project2.setDomains(domainList);
		decorateMtmDomainList(project.getDataDomains(),domainList);
		project2.setDataDomains(project.getDataDomains());
		dbdg.setDomains(domainList);

		axml.setDomainList(domainList);
		axml.setDbname(project2.getDbName());
		axml.setDbUsername(project2.getDbUsername());
		axml.setDbPassword(project2.getDbPassword());
		axml.setEmptypassword(project2.isEmptypassword());

		axml.setDbType(project2.getDbType());

		axml.setControllers(mycontrollers);
		if (project.isEmptypassword()) {
			axml.setDbPassword("");
			axml.setEmptypassword(true);
		}
		List<String> packageToScanList = new ArrayList<String>();
		packageToScanList.add(project.getPackageToken() + ".domain");
		axml.setPackagesToScanList(packageToScanList);
		axml.setPutInsideSrcAndClasses(true);

		project2.addDBDefinitionGenerator(dbdg);
		project2.setPrisms(prismList);
		project2.replaceConfigFile(axml);

		return project2;
	}

	public Project translateProjectMetaData(HSSFSheet metaSheet, Project project) {
		String dbtype = readMetaField(metaSheet, "dbtype", "mysql");
		String projectName = readMetaField(metaSheet, "project", dbtype);
		String packageToken = readMetaField(metaSheet, "packagetoken", dbtype);
		String dbprefix = readMetaField(metaSheet, "dbprefix", dbtype);
		String dbname = readMetaField(metaSheet, "dbname", dbtype);
		String dbusername = readMetaField(metaSheet, "dbusername", dbtype);
		String dbpassword = readMetaField(metaSheet, "dbpassword", dbtype);
		String title = readMetaField(metaSheet, "title", dbtype);
		String subTitle = readMetaField(metaSheet, "subtitle", dbtype);
		String footer = readMetaField(metaSheet, "footer", dbtype);
		String crossOrigin = readMetaField(metaSheet, "crossorigin", dbtype);
		String resolution = readMetaField(metaSheet, "resolution", dbtype);

		String technicalstack = readMetaField(metaSheet, "technicalstack", dbtype);
		project.setStandardName(projectName);
		project.setPackageToken(packageToken);
		project.setDbPrefix(dbprefix);
		project.setDbName(dbname);
		project.setDbUsername(dbusername);
		project.setDbPassword(dbpassword);
		project.setDbType(dbtype);
		project.setTechnicalstack(technicalstack);
		project.setTitle(title);
		project.setSubTitle(subTitle);
		project.setFooter(footer);
		project.setCrossOrigin(crossOrigin);
		project.setResolution(resolution);
		return project;
	}

	public Prism translatePrism(HSSFSheet prismSheet) {
		Prism prism = new Prism();
		return prism;
	}

	public Domain translateDomain(HSSFSheet domainSheet, String dbType) throws ValidateException {
		Domain domain = new Domain();
		String domainName = readMetaField(domainSheet, "domain", dbType);
		String plural = readMetaField(domainSheet, "plural", dbType);
		String domainlabel = readMetaField(domainSheet, "domainlabel", dbType);
		Cell metaFieldCell = locateKeyCell(domainSheet, "元字段类型", dbType);
		Cell fieldCell = locateKeyCell(domainSheet, "字段", dbType);
		Cell fieldTypeCell = locateKeyCell(domainSheet, "字段类型", dbType);
		Cell fieldLabelCell = locateKeyCell(domainSheet, "字段标签", dbType);
		Cell fieldDataCell = locateKeyCell(domainSheet, "数据", dbType);
		ValidateInfo info = new ValidateInfo();
		for (int i = metaFieldCell.getColumnIndex() + 1; i < domainSheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			try {
				String metaField = readFieldMeta(domainSheet, i, metaFieldCell.getRowIndex(),dbType);
				if (!StringUtil.isBlank(metaField)
						&& (metaField.equalsIgnoreCase("field") || metaField.equalsIgnoreCase("id")
								|| metaField.equalsIgnoreCase("domainid") || metaField.equalsIgnoreCase("domainname")
								|| metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
					Field f = readDomainField(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex(), domain,dbType);
					if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("field")) {
						domain.addField(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("id") || metaField.equalsIgnoreCase("domainid"))) {
						domain.setDomainId(f);
					} else if (!StringUtil.isBlank(metaField)
							&& (metaField.equalsIgnoreCase("active") || metaField.equalsIgnoreCase("activefield"))) {
						domain.setActive(f);
					} else if (!StringUtil.isBlank(metaField) && (metaField.equalsIgnoreCase("domainname")
							|| metaField.equalsIgnoreCase("activefield"))) {
						domain.setDomainName(f);
					}
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("dropdown")) {
					Dropdown dp = readDropdown(domainSheet, i, metaFieldCell.getRowIndex(), fieldCell.getRowIndex(),
							fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					domain.addField(dp);
					domain.putFieldLabel(dp.getFieldName(), dp.getLabel());
				} else if (!StringUtil.isBlank(metaField) && metaField.equalsIgnoreCase("manytomanyslave")) {
					String mtmname = readManyToManyName(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAlias = readManyToManyAlias(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					String mtmAliasLabel = readManyToManyAliasLabel(domainSheet, i, metaFieldCell.getRowIndex(),
							fieldCell.getRowIndex(), fieldTypeCell.getRowIndex(), fieldLabelCell.getRowIndex());
					ManyToMany mtm = new ManyToMany();
					if (!StringUtil.isBlank(mtmname)){
						mtm = new ManyToMany(domainName,mtmname);
					}
					if (!StringUtil.isBlank(mtmAlias)){
						mtm.setSlaveAlias(mtmAlias);
						mtm.setSlaveAliasLabel(mtmAliasLabel);
						mtm.setStandardName(mtmAlias);
					}
					domain.addManyToMany(mtm);
				}
			} catch (ValidateException e) {
				info.addAllCompileErrors(e.getValidateInfo().getCompileErrors());
				info.addAllCompileWarnings(e.getValidateInfo().getCompileWarnings());
			}
		}
		if (!info.success())
			throw new ValidateException(info);
		domain.setStandardName(domainName);
		if (!StringUtil.isBlank(plural))
			domain.setPlural(plural);
		if (!StringUtil.isBlank(domainlabel))
			domain.setLabel(domainlabel);

		return domain;
	}

	public Field readDomainField(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex, Domain domain,String dbType) throws ValidateException {
		Field f = new Field();
		String metafield = readFieldMeta(sheet, columIndex, metaFieldIndex,dbType);
		System.out.println("JerryDebug:" + metafield);
		String fieldname = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldLabel = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");

		ValidateInfo info = new ValidateInfo();
		if (!StringUtil.isLowerCaseLetter(fieldname)) {
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "未使用小写英文字母开头！");
		}
		if (fieldname.length() >= 2 && !StringUtil.isLowerCaseLetterPosition(fieldname,1)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "第二个字母未使用小写英文字母！");
		}
		if (isForbidden(fieldname)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "使用了被禁止的单词！");
		}
		if (isSqlKeyword(fieldname)){
			info.addCompileError("域对象"+domain.getStandardName()+"字段" + fieldname + "使用了SQL关键字！");
		}
		
		if (!StringUtil.isBlank(metafield)
				&& (metafield.equalsIgnoreCase("id") || metafield.equalsIgnoreCase("domianid")
						|| metafield.equalsIgnoreCase("domainname") || metafield.equalsIgnoreCase("active")
						|| metafield.equalsIgnoreCase("activefield") || metafield.equalsIgnoreCase("field"))) {
			if (!StringUtil.isBlank(fieldType))
				f.setFieldType(fieldType);
			if (!StringUtil.isBlank(fieldLabel)) {
				f.setLabel(fieldLabel);
				domain.putFieldLabel(fieldname, fieldLabel);
			}
			if (!StringUtil.isBlank(fieldname))
				f.setFieldName(fieldname);
			if (!info.success())
				throw new ValidateException(info);
			else
				return f;
		} else {
			info.addCompileError("字段解析错误");
			throw new ValidateException(info);
		}
	}

	public Dropdown readDropdown(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String fieldname = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		String fieldLabel = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim().replace("\'","");
		ValidateInfo info = new ValidateInfo();
		if (!StringUtil.isLowerCaseLetter(fieldname)) {
			info.addCompileError("下拉列表字段" + fieldname + "未使用小写英文字母开头！");
		}
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("dropdown")) {
			Dropdown dp = new Dropdown(fieldType);
			dp.setAliasName(fieldname);
			dp.setFieldName(dp.getAliasName());
			dp.setLabel(fieldLabel);
			if (!info.success())
				throw new ValidateException(info);
			else
				return dp;
		} else {
			info.addCompileError("字段解析错误");
			throw new ValidateException(info);
		}
	}

	public String readFieldMeta(HSSFSheet sheet, int columIndex, int metaFieldIndex, String dbType) {
		String metafield = getCellStringValue(sheet.getRow(metaFieldIndex).getCell(columIndex),dbType);
		return metafield;
	}

	public String readManyToManyName(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String fieldType = sheet.getRow(fieldTypeIndex).getCell(columIndex).getStringCellValue().trim();
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return fieldType;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}
	
	public String readManyToManyAlias(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String field = sheet.getRow(fieldIndex).getCell(columIndex).getStringCellValue().trim();
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return field;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}
	
	public String readManyToManyAliasLabel(HSSFSheet sheet, int columIndex, int metaFieldIndex, int fieldIndex,
			int fieldTypeIndex, int fieldLabelIndex) throws ValidateException {
		String metafield = sheet.getRow(metaFieldIndex).getCell(columIndex).getStringCellValue().trim();
		String field = sheet.getRow(fieldLabelIndex).getCell(columIndex).getStringCellValue().trim();
		if (metafield != null && !metafield.equals("") && metafield.equalsIgnoreCase("manytomanyslave")) {
			return field;
		} else {
			throw new ValidateException("字段解析错误");
		}
	}

	public String readMetaField(HSSFSheet metaSheet, String key, String dbType) {
		Cell c = locateKeyCell(metaSheet, key, dbType);
		if (c == null)
			return "";
		else
			// return
			// metaSheet.getRow(c.getRowIndex()).getCell(c.getColumnIndex() +
			// 1).getStringCellValue();
			return this.getCellStringValue(metaSheet.getRow(c.getRowIndex()).getCell(c.getColumnIndex() + 1), dbType).trim();
	}

	public Cell locateKeyCell(HSSFSheet metaSheet, String key, String dbType) {
		int rowbegin = metaSheet.getFirstRowNum();
		int rowend = metaSheet.getLastRowNum();
		for (int i = rowbegin; i <= rowend; i++) {
			Row r = metaSheet.getRow(i);
			for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) {
				Cell c = r.getCell(j);
				if (c != null && this.getCellStringValue(c, dbType).equalsIgnoreCase(key))
					return c;
			}
		}
		return null;
	}

	public List<Domain> readDomainListWithData(HSSFSheet sheet, Domain templateDomain, String dbtype) throws Exception {
		List<Domain> resultList = new ArrayList<Domain>();
		Cell dataCell = locateKeyCell(sheet, "数据", dbtype);
		Cell fieldCell = locateKeyCell(sheet, "字段", dbtype);
		for (int i = dataCell.getRowIndex(); i < findOutLastDataRowIndex(sheet, findOutIdColIndex(sheet, dbtype),
				dataCell.getRowIndex(), dbtype); i++) {
			Domain targetDomain = (Domain) templateDomain.deepClone();
			for (Field f : templateDomain.getFields()) {
				if (f instanceof Dropdown) {
					Dropdown dp = (Dropdown) f;
					String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, dp.getAliasName(),
							fieldCell.getColumnIndex() + 1, fieldCell.getRowIndex(), i, dbtype));
					System.out.println("JerryDebug:dropdown:fieldvalue:" + fieldValue);
					if (!StringUtil.isBlank(fieldValue))
						targetDomain.setFieldValue(dp.getAliasName(), fieldValue);
					else
						targetDomain.setFieldValue(dp.getAliasName(), fieldValue);

					System.out.println(
							"JerryDebug:dropdown:value:" + targetDomain.getField(dp.getAliasName()).getFieldValue());
				} else {
					String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, f.getFieldName(),
							fieldCell.getColumnIndex() + 1, fieldCell.getRowIndex(), i, dbtype));
					System.out.println("JerryDebug:readFieldWithData:field:fieldValue"+f.getFieldName()+":"+fieldValue);
					if (!StringUtil.isBlank(fieldValue))
						targetDomain.getField(f.getFieldName()).setFieldValue(fieldValue);
					else
						targetDomain.getField(f.getFieldName()).setFieldValue("");
				}
			}
			for (ManyToMany mtm:targetDomain.getManyToManies()){
				String fieldValue = StringUtil.filterSingleQuote(readDomainFieldValue(sheet, mtm.getSlaveAlias(),
						fieldCell.getColumnIndex() + 1, fieldCell.getRowIndex(), i, dbtype));
				
				mtm.setMaster(targetDomain);					
				mtm.setValues(fieldValue);
				mtm.setMasterValue(targetDomain.getDomainId().getFieldValue());
			}
			resultList.add(targetDomain);
		}
		return resultList;
	}
	
	public void decorateMtmDomainList(List<List<Domain>> dataDomainList,List<Domain> allDomainList) throws Exception{
		for (List<Domain> targets:dataDomainList){
			for (Domain targetDomain: targets){
				for (ManyToMany mtm:targetDomain.getManyToManies()){
					mtm.setSlave(findDomainFromListByStandardName(allDomainList,mtm.getManyToManySalveName()));
				}
			}
		}
	}

	public int findOutIdColIndex(HSSFSheet sheet, String dbType) {
		Cell metaFieldCell = locateKeyCell(sheet, "元字段类型", dbType);
		for (int i = metaFieldCell.getColumnIndex() + 1; i < sheet.getRow(metaFieldCell.getRowIndex())
				.getLastCellNum(); i++) {
			if (sheet.getRow(metaFieldCell.getRowIndex()).getCell(i).getStringCellValue().equals("id")) {
				return i;
			}
		}
		return metaFieldCell.getColumnIndex() + 1;
	}

	public int findOutLastDataRowIndex(HSSFSheet sheet, int idColIndex, int beginRowIndex, String dbtype) {
		for (int i = beginRowIndex; i <= sheet.getLastRowNum(); i++) {
			if (sheet.getRow(i)==null ||sheet.getRow(i).getCell(idColIndex)==null || StringUtil.isBlank(getCellStringValue(sheet.getRow(i).getCell(idColIndex), dbtype)))
				return i;
		}
		return sheet.getLastRowNum()+1;
	}

	public String readDomainFieldValue(HSSFSheet sheet, String fieldName, int beginColIndex, int fieldNameRowIndex,
			int rowIndex, String dbtype) {
		for (int i = beginColIndex; i < sheet.getRow(fieldNameRowIndex).getLastCellNum(); i++) {
			Cell c = sheet.getRow(fieldNameRowIndex).getCell(i);
			String cellfieldName = c.getStringCellValue();
			if (!StringUtil.isBlank(cellfieldName) && cellfieldName.equals(fieldName)) {
				return getCellStringValue(sheet.getRow(rowIndex).getCell(i), dbtype);
			}
		}
		return "";
	}

	public String getCellStringValue(Cell c, String dbtype) {
		if (c.getCellType() == CellType.STRING)
			return c.getStringCellValue();
		else if (c.getCellType() == CellType.NUMERIC) {
			short format = c.getCellStyle().getDataFormat();
			if (format == 14 || format == 31 || format == 57 || format == 58) {
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			} else if (format == 20 || format == 32) {
				DateFormat formater = new SimpleDateFormat("HH:mm");
				Date date = DateUtil.getJavaDate(c.getNumericCellValue());
				String value = formater.format(date);
				return value;
			}
			double dis = c.getNumericCellValue() - Math.round(c.getNumericCellValue());
			if (dis > 0.0001d) {
				return "" + c.getNumericCellValue();
			} else {
				return "" + (long) Math.round(c.getNumericCellValue());
			}
		} else if ((StringUtil.isBlank(dbtype) || dbtype.equalsIgnoreCase("mysql"))
				&& c.getCellType() == CellType.BOOLEAN) {
			return "" + c.getBooleanCellValue();
		}else if ((!StringUtil.isBlank(dbtype) && dbtype.equalsIgnoreCase("oracle"))
				&& c.getCellType() == CellType.BOOLEAN) {
			return "" + BooleanUtil.parseBooleanInt("" + c.getBooleanCellValue());
		}else if (c.getCellType() == CellType.FORMULA) {
			if (StringUtil.isBlank(dbtype) || dbtype.equalsIgnoreCase("mysql")){
				String fml = c.getCellFormula();
				if (fml.equalsIgnoreCase("TRUE")) return "true";
				else if (fml.equalsIgnoreCase("FALSE")) return "false";
				else return "";
			}else if (!StringUtil.isBlank(dbtype) && dbtype.equalsIgnoreCase("oracle")) {
				String fml = c.getCellFormula();
				if (fml.equalsIgnoreCase("TRUE")) return "1";
				else if (fml.equalsIgnoreCase("FALSE)")) return "0";
				else return "";
			}
			return "";
		}else {
			return "";
		}
	}

	public Action mapRowToAction(Row row) {
		Action ac = new Action();
		String serial = "";
		String desc = "";
		String action = "";
		String xpath = "";
		String value = "";
		String ignore = "";

		if (row.getCell(1) != null)
			serial = row.getCell(1).toString();
		if (row.getCell(2) != null)
			desc = row.getCell(2).toString();
		if (row.getCell(3) != null)
			action = row.getCell(3).toString();
		if (row.getCell(4) != null)
			xpath = row.getCell(4).toString();
		if (row.getCell(5) != null)
			value = row.getCell(5).toString();
		if (row.getCell(6) != null)
			ignore = row.getCell(6).toString();
		ac.setTestCaseSerial(serial);
		ac.setTestCaseDescription(desc);
		ac.setName(action);
		ac.setXpath(xpath);
		ac.setValue(value);
		ac.setIsIgnore(ignore);
		return ac;
	}

	public static void main(String[] args) {
		try {
			InputStream is = new FileInputStream(
					"C:\\\\Users\\\\jerry.shen03\\\\git\\\\PeaceWingSMEU\\\\PeaceWingSMEU\\\\src\\\\org\\\\javaforever\\\\poitranslator\\\\core\\\\GenerateSample.xls");
			POIFSFileSystem fs = new POIFSFileSystem(is);
			HSSFWorkbook wb = new HSSFWorkbook(fs);

			ProjectExcelWorkBook pwb = new ProjectExcelWorkBook();
			Project pj = pwb.translate(wb);
			// System.out.println(pj.toString());
			System.out.println("=============");
			System.out.println(pj.getStandardName());
			System.out.println(pj.getPackageToken());
			System.out.println(pj.getDbPrefix());
			System.out.println(pj.getDbName());
			System.out.println(pj.getDbUsername());
			System.out.println(pj.getDbPassword());
			System.out.println(pj.getDbType());
			System.out.println(pj.getTechnicalstack());

			List<Domain> ds = pj.getDomains();
			for (Domain d : ds) {
				System.out.println("++++++++++++++++++++");
				System.out.println(d.getStandardName());
				System.out.println(d.getPlural());
				System.out.println(d.getLabel());
				for (Field f : d.getFields()) {
					System.out.println(f.getFieldName());
				}
				for (ManyToMany mtm : d.getManyToManies()) {
					System.out.println(mtm.getManyToManySalveName());
				}
			}

			// List<Prism> ps = pj.getPrisms();
			// for (Prism p:ps){
			// System.out.println("---------------------");
			// System.out.println(p.toString());
			// }
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
